package com.thinkingstar.iads.common.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.swing.filechooser.FileSystemView;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFCreationHelper;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFTable;
import org.apache.poi.xwpf.usermodel.XWPFTableCell;
import org.nutz.lang.util.NutMap;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.*;

public class ExcelPoiUtil {
	
	public static void main(String[] args) throws IOException {
		createExcel();
	}
	
	/**
	 * 将excel解析为标准数据
	 * @param excel_file
	 * @return
	 * @throws IOException 
	 * @throws InvalidFormatException 
	 * @throws EncryptedDocumentException 
	 */
	public static List<Map<Integer, Object>> getDataListByExcel(File excel_file) throws Exception{
		FileInputStream fis = new FileInputStream(excel_file);
		HSSFWorkbook workbook = new HSSFWorkbook(fis);
		HSSFSheet sheet = workbook.getSheetAt(0);
		Iterator<Row> row_iterator = sheet.rowIterator();
		List <Map <Integer , Object>> data_list = new ArrayList<>();
		while (row_iterator.hasNext()) {
			Map <Integer , Object> data_map = new HashMap<>();
			Row row = row_iterator.next();
			Iterator<Cell> cell_iterator = row.cellIterator();
			while (cell_iterator.hasNext()) {
				Cell cell = cell_iterator.next();
				cell.setCellType(Cell.CELL_TYPE_STRING);
				String cell_value = cell.getStringCellValue();
				String data_value = StringUtil.removeTrim(cell_value);
				Integer col_index = cell.getColumnIndex();
				data_map.put(col_index, data_value);
			}
			data_list.add(data_map);
		}
		return data_list;
	}
	
	public static HSSFCellStyle getOneStyle(HSSFWorkbook workbook) {
		// 产生Excel表头
		HSSFCellStyle style = workbook.createCellStyle();
		style.setBorderLeft((short) 1); // 左边框
		style.setBorderRight((short) 1); // 右边框
		style.setBorderBottom((short) 1);
		style.setBorderTop((short) 1);
		return style;
	}
	
	
	/**
	 * 
	 * @param file_name 生成的excel临时文件路径
	 * @param header_list excel表头
	 * @throws IOException 
	 */
	public static File createExcel(String file_name , List <NutMap> header_list) throws IOException{
		String relative_path = POIFileUtil.getExportExcelFileName(file_name);
		String absolute_path = POIFileUtil.getAbFileNameByRepath(relative_path);
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 用于格式化单元格的数据
		DataFormat format = workbook.createDataFormat();
		// 设置字体
		Font font = workbook.createFont();
	    font.setFontHeightInPoints((short) 30); // 字体高度
	    font.setColor(Font.COLOR_RED); // 字体颜色
	    font.setFontName("黑体"); // 字体
	    font.setBoldweight(Font.BOLDWEIGHT_BOLD); // 宽度
		HSSFSheet sheet = workbook.createSheet("Sheet1");
		HSSFRow row = sheet.createRow(0);
		for(int i=0;i<header_list.size();i++){
			NutMap head_map = header_list.get(i);
			row.createCell(i).setCellValue(head_map.getString("field_comment"));//表头名称
		}
		row.setHeightInPoints(30);
	    workbook.setActiveSheet(0);
	    File excel_file = POIFileUtil.createFile(workbook, absolute_path); //将workbook写入文件
	    return excel_file;
	}

	/**
	 * 生成提入资料和设计单元excel （带数据）
	 * */
	public static File createExcel(String file_name,String project_id,String type,List<NutMap> header_list,List<NutMap> dataList) throws  IOException {
		String filePath = POIFileUtil.getFilePathByType(type,project_id);
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 用于格式化单元格的数据
		DataFormat format = workbook.createDataFormat();
		// 设置字体
		Font font = workbook.createFont();
		font.setFontHeightInPoints((short) 30); // 字体高度
		font.setColor(Font.COLOR_RED); // 字体颜色
		font.setFontName("黑体"); // 字体
		font.setBoldweight(Font.BOLDWEIGHT_BOLD); // 宽度
		HSSFSheet sheet = workbook.createSheet("Sheet1");
		HSSFRow row = sheet.createRow(0);
		for(int i=0;i<header_list.size();i++){
			NutMap head_map = header_list.get(i);
			row.createCell(i).setCellValue(head_map.getString("field_comment"));//表头名称
		}
		if(dataList != null)
		{
			for(int j=0;j<dataList.size();j++)
			{
				NutMap data_map = dataList.get(j);
				//创建数据行,前面有两行,头标题行和列标题行
				HSSFRow row2 = sheet.createRow(j+1);
				for(int n=0;n<header_list.size();n++){
					NutMap head_map = header_list.get(n);
					HSSFCell cell = row2.createCell(n);
					cell.setCellValue(data_map.getString(head_map.getString("field_name")));
				}
			}
		}
		row.setHeightInPoints(30);
		workbook.setActiveSheet(0);
		File excel_file = POIFileUtil.createFile(workbook, filePath+File.separator+file_name); //将workbook写入文件
		return excel_file;
	}

	/**
	 * 生成提入资料和设计单元excel （带数据 及 汇总）
	 * */
	public static File createExcelAddCount(String file_name,String project_id,String type,List<NutMap> header_list,List<NutMap> dataList,List<Map> countList) throws  IOException {
		String filePath = POIFileUtil.getFilePathByType(type,project_id);
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 用于格式化单元格的数据
		DataFormat format = workbook.createDataFormat();
		// 设置字体
		Font font = workbook.createFont();
		font.setFontHeightInPoints((short) 30); // 字体高度
		font.setColor(Font.COLOR_RED); // 字体颜色
		font.setFontName("黑体"); // 字体
		font.setBoldweight(Font.BOLDWEIGHT_BOLD); // 宽度
		HSSFSheet sheet = workbook.createSheet("Sheet1");
		HSSFRow row = sheet.createRow(0);
		for(int i=0;i<header_list.size();i++){
			NutMap head_map = header_list.get(i);
			row.createCell(i).setCellValue(head_map.getString("field_comment"));//表头名称
		}
		if(dataList != null)
		{
			for(int j=0;j<dataList.size();j++)
			{
				NutMap data_map = dataList.get(j);
				//创建数据行,前面有两行,头标题行和列标题行
				HSSFRow row2 = sheet.createRow(j+1);
				for(int n=0;n<header_list.size();n++){
					NutMap head_map = header_list.get(n);
					HSSFCell cell = row2.createCell(n);
					cell.setCellValue(data_map.getString(head_map.getString("field_name")));
				}
			}
		}
		if (countList!=null && countList.size()>0) {
			HSSFRow row2 = sheet.createRow(1+dataList.size());
			for(int n=0;n<header_list.size();n++) {
				NutMap head_map = header_list.get(n);
				HSSFCell cell = row2.createCell(n);
				for (int i = 0; i < countList.size(); i++) {
					Map data_map = countList.get(i);
					if (head_map.getString("field_name").equals("serial_number")){
						cell.setCellValue("汇总");
					} else if(data_map.containsKey(head_map.getString("field_name"))) {
						cell.setCellValue((String) data_map.get(head_map.getString("field_name")));
					} else {
						cell.setCellValue("");
					}
				}
			}
		}
		row.setHeightInPoints(30);
		workbook.setActiveSheet(0);
		File excel_file = POIFileUtil.createFile(workbook, filePath+File.separator+file_name); //将workbook写入文件
		return excel_file;
	}

	/**
	 * 生成提入资料和设计单元word （带数据 及汇总）
	 * */
	public static File createWordAddCount(String file_name,String project_id,String type,List<NutMap> header_list,List<NutMap> dataList,List<Map> countList) throws IOException {
		String filePath = POIFileUtil.getFilePathByType(type,project_id);
		XWPFDocument docx = new XWPFDocument();
		int row = dataList.size() + 1+countList.size();
		XWPFTable table = docx.createTable(row, header_list.size()); // 几行 几列
		// 设置上下左右四个方向的距离，可以将表格撑大
		CTTbl ttbl = table.getCTTbl();
		CTTblPr tblPr = ttbl.getTblPr() == null ? ttbl.addNewTblPr() : ttbl.getTblPr();
		CTTblWidth tblWidth = tblPr.isSetTblW() ? tblPr.getTblW() : tblPr.addNewTblW();
		CTJc cTJc = tblPr.addNewJc();
		cTJc.setVal(STJc.Enum.forString("center"));
		tblWidth.setW(new BigInteger("8000"));
		tblWidth.setType(STTblWidth.NIL);
		List<XWPFTableCell> index = table.getRow(0).getTableCells(); // 设置表头
		for(int i=0;i<header_list.size();i++){
			NutMap head_map = header_list.get(i);
			index.get(i).setText(head_map.getString("field_comment"));
		}
		List<XWPFTableCell> tableCells = null;
		for(int j=0;j<dataList.size();j++)
		{
			tableCells = table.getRow(j+1).getTableCells();
			NutMap data_map = dataList.get(j);
			for(int n=0;n<header_list.size();n++){
				NutMap head_map = header_list.get(n);
				tableCells.get(n).setText(data_map.getString(head_map.getString("field_name")));
			}
		}
		if (countList!=null && countList.size()>0) {
			tableCells = table.getRow(1+dataList.size()).getTableCells();
			for(int n=0;n<header_list.size();n++) {
				NutMap head_map = header_list.get(n);
				for (int i = 0; i < countList.size(); i++) {
					Map data_map = countList.get(i);
					if (head_map.getString("field_name").equals("serial_number")){
						tableCells.get(n).setText("汇总");
					} else if(data_map.containsKey(head_map.getString("field_name"))) {
						tableCells.get(n).setText((String) data_map.get(head_map.getString("field_name")));
					} else {
						tableCells.get(n).setText("");
					}
				}
			}
		}
		File docFile = POIFileUtil.createFile(docx, filePath,file_name);
		return docFile;

	}

	/**
	 * 生成提入资料和设计单元word （带数据）
	 * */
	public static File createWord(String file_name,String project_id,String type,List<NutMap> header_list,List<NutMap> dataList) throws IOException {
		String filePath = POIFileUtil.getFilePathByType(type,project_id);
		XWPFDocument docx = new XWPFDocument();
		int row = dataList.size() + 1;
		XWPFTable table = docx.createTable(row, header_list.size()); // 几行 几列
		// 设置上下左右四个方向的距离，可以将表格撑大
		CTTbl ttbl = table.getCTTbl();
		CTTblPr tblPr = ttbl.getTblPr() == null ? ttbl.addNewTblPr() : ttbl.getTblPr();
		CTTblWidth tblWidth = tblPr.isSetTblW() ? tblPr.getTblW() : tblPr.addNewTblW();
		CTJc cTJc = tblPr.addNewJc();
		cTJc.setVal(STJc.Enum.forString("center"));
		tblWidth.setW(new BigInteger("8000"));
		tblWidth.setType(STTblWidth.NIL);
		List<XWPFTableCell> index = table.getRow(0).getTableCells(); // 设置表头
		for(int i=0;i<header_list.size();i++){
			NutMap head_map = header_list.get(i);
			index.get(i).setText(head_map.getString("field_comment"));
		}
		List<XWPFTableCell> tableCells = null;
		for(int j=0;j<dataList.size();j++)
		{
			tableCells = table.getRow(j+1).getTableCells();
			NutMap data_map = dataList.get(j);
			for(int n=0;n<header_list.size();n++){
				NutMap head_map = header_list.get(n);
				tableCells.get(n).setText(data_map.getString(head_map.getString("field_name")));
			}
		}
		File docFile = POIFileUtil.createFile(docx, filePath,file_name);
		return docFile;

	}

	public static void createExcel() throws IOException{
	    // 获取桌面路径
	    FileSystemView fsv = FileSystemView.getFileSystemView();
	    String desktop = fsv.getHomeDirectory().getPath();
	    String filePath = desktop + "/template.xls";

	    File file = new File(filePath);
	    OutputStream outputStream = new FileOutputStream(file);
	    HSSFWorkbook workbook = new HSSFWorkbook();
	    HSSFSheet sheet = workbook.createSheet("Sheet1");
	    HSSFRow row = sheet.createRow(0);
	    row.createCell(0).setCellValue("id");
	    row.createCell(1).setCellValue("订单号");
	    row.createCell(2).setCellValue("下单时间");
	    row.createCell(3).setCellValue("个数");
	    row.createCell(4).setCellValue("单价");
	    row.createCell(5).setCellValue("订单金额");
	    row.setHeightInPoints(30); // 设置行的高度

	    HSSFRow row1 = sheet.createRow(1);
	    row1.createCell(0).setCellValue("1");
	    row1.createCell(1).setCellValue("NO00001");

	    // 日期格式化
	    HSSFCellStyle cellStyle2 = workbook.createCellStyle();
	    HSSFCreationHelper creationHelper = workbook.getCreationHelper();
	    cellStyle2.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
	    sheet.setColumnWidth(2, 20 * 256); // 设置列的宽度

	    HSSFCell cell2 = row1.createCell(2);
	    cell2.setCellStyle(cellStyle2);
	    cell2.setCellValue(new Date());

	    row1.createCell(3).setCellValue(2);


	    // 保留两位小数
	    HSSFCellStyle cellStyle3 = workbook.createCellStyle();
	    cellStyle3.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
	    HSSFCell cell4 = row1.createCell(4);
	    cell4.setCellStyle(cellStyle3);
	    cell4.setCellValue(29.5);


	    // 货币格式化
	    HSSFCellStyle cellStyle4 = workbook.createCellStyle();
	    HSSFFont font = workbook.createFont();
	    font.setFontName("华文行楷");
	    font.setFontHeightInPoints((short)15);
	    font.setColor(HSSFColor.RED.index);
	    cellStyle4.setFont(font);

	    HSSFCell cell5 = row1.createCell(5);
	    cell5.setCellFormula("D2*E2");  // 设置计算公式

	    // 获取计算公式的值
	    HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(workbook);
	    cell5 = e.evaluateInCell(cell5);
	    System.out.println(cell5.getNumericCellValue());
	    workbook.setActiveSheet(0);
	    workbook.write(outputStream);
	    outputStream.close();
	}

}
